<?php
/**
 * Created by PhpStorm.
 * User: lixy
 * Date: 2016/1/21
 * Time: 10:10
 */

namespace app\models;

use app\lib\BaseModel;

class HomeDao extends BaseModel
{
    //type:card/bag status:1/2
    public static function getSendRecordList($userid,$start=0,$pagesize=6,$type='',$status='',$begintime='',$endtime=''){
        $typeSql='';
        if($type!=''){
            $typeSql=" and x.type='$type' ";
        }
        $statusSql='';
        if($status!=''){
            $statusSql=" and x.status=$status ";
        }
        $timeSql='';
        if($begintime!='' && $endtime!=''){
            $timeSql=" and substr(x.create_datetime,1,10)>='$begintime' and substr(x.create_datetime,1,10)<='$endtime' ";
        }

        $sql="  select x.*,u.nick_name from
           (select 'card' type,id ordercode,create_datetime,user_id,status,num,price,name,valid_date_message from
              (select o.*,c.name,valid_date_message from t_wxmall_order o, t_wxmall_doracard c where o.card_id=c.id)
            where 1=1
            union
            select 'bag' type, code ordercode,create_datetime,user_id,status,1  num,price,Bag_name name,'长期' valid_date_message  from  t_wxmall_dorabag_order
            ) x,t_wxmall_user u where x.user_id=:user_id and x.user_id=u.id $typeSql $statusSql $timeSql order by x.create_datetime desc
        ";

        $cmd=  static::query()->createCommand(static::pageSql($sql,$start,$pagesize));
        $cmd->bindValue(':user_id',$userid);
        return $cmd->queryAll();
    }
    //type:card/bag status:1/2
    public static function getReceiveRecordList($userid,$start=0,$pagesize=6,$type='',$status='',$begintime='',$endtime=''){
        $typeSql='';
        if($type!=''){
            $typeSql=" and x.type='$type' ";
        }
        $statusSql='';
        if($status!=''){
            $statusSql=" and x.status=$status ";
        }
        $timeSql='';
        if($begintime!='' && $endtime!=''){
            $timeSql=" and substr(x.create_datetime,1,10)>='$begintime' and substr(x.create_datetime,1,10)<='$endtime' ";
        }

        $sql="  select x.*,u.nick_name from
           (
                  select 'card' type,u.card_code_id code_id,u.create_datetime,u.user_id,d.name,d.valid_date_message
                , c.status
                 , o.user_id m_user_id
                 from t_wxmall_user_doracard_code u,t_wxmall_doracard_code  c ,t_wxmall_doracard d,t_wxmall_order o
                 where u.card_code_id=c.id and c.card_id=d.id and o.id=c.order_id

                 union

                select 'bag' type,og.id code_id,og.user_get_datetime create_datetime,og.user_id,
                o.bag_name name,'长期' valid_date_message,og.status,o.user_id m_user_id
                 from T_WXMALL_DORABAG_ORDER_GOODS og,t_wxmall_dorabag_order o
                 where og.bag_order_id=o.code and og.status!=0
            ) x , t_wxmall_user u  where x.user_id=:user_id and  x.m_user_id=u.id(+)  $typeSql $statusSql $timeSql order by x.create_datetime desc
        ";
//        echo $sql;
//        die;
        $cmd=  static::query()->createCommand(static::pageSql($sql,$start,$pagesize));
        $cmd->bindValue(':user_id',$userid);
        return $cmd->queryAll();
    }


/*
P_RECEIVE_DORACOIN --卡或礼包产品兑换成朵拉币
(
USERID IN VARCHAR2          --用户id
, TYPEID IN NUMBER            --1card  2bag
, ORDERID IN VARCHAR2         --订单编号 w开头的
, EXCHANGEID IN VARCHAR2      --子编码：good_order_id（bag）兑换码（card）
, RESULTS OUT VARCHAR2        --返回值 1成功 0失败
)*/

    public static function P_RECEIVE_DORACOIN($USERID,$TYPEID,$ORDERID,$EXCHANGEID){
        $cmd=  static::query()->createCommand('call P_RECEIVE_DORACOIN(:USERID,:TYPEID,:ORDERID,:EXCHANGEID,:RESULTS)');
        $cmd->bindValue(':USERID',$USERID);
        $cmd->bindValue(':TYPEID',$TYPEID);
        $cmd->bindValue(':ORDERID',$ORDERID);
        $cmd->bindValue(':EXCHANGEID',$EXCHANGEID);
        $cmd->bindParam(':RESULTS', $RESULTS,\PDO::PARAM_STR,50);
        $cmd->execute();
//        Yii::trace($orderid);
        return $RESULTS;
    }
//查询朵拉币信息  type：1card  2bag
    public static function get_DORACOIN($ORDER_CODE,$CHILD_CODE,$TYPE,$USER_ID){
        $cmd=  static::query()->createCommand('select * from T_WXMALL_DORACOIN_RECORD where
        ORDER_CODE=:ORDER_CODE and CHILD_CODE=:CHILD_CODE and TYPE=:TYPE and USER_ID=:USER_ID');
        $cmd->bindValue(':ORDER_CODE',$ORDER_CODE);
        $cmd->bindValue(':CHILD_CODE',$CHILD_CODE);
        $cmd->bindValue(':TYPE',$TYPE);
        $cmd->bindValue(':USER_ID',$USER_ID);
        return $cmd->queryAll();
    }
/*P_WXMALL_INVOICE_DETAIL  --发票明细
(
DOBJECT_TYPE IN VARCHAR2       --开票对象 1：朵拉标准卡 2：朵拉礼品包
, DOBJECT_ID IN VARCHAR2         --订单编号
, DTYPE IN VARCHAR2              --发票类型 0：未开票 1：普通纸质发票 2：增值税发票
, DINVOICE_TITLE IN VARCHAR2      --发票抬头
, DRECEIVER_NAME IN VARCHAR2     --收票人姓名
, DRECEIVER_PHONE IN VARCHAR2    -- 收票人手机
, DRECEIVER_ADDRESS IN VARCHAR2  --收票人地址
, DREMARK IN VARCHAR2            --备注
, DFREIGHT IN NUMBER             --快递费
, DCONTENT_TYPE IN NUMBER         --是否开发票明细 0 需要，非0不需要
, RESULTS OUT VARCHAR2           --返回值
)  1成功，0失败*/

    //提交发票信息
    public static function P_WXMALL_INVOICE_DETAIL($DOBJECT_TYPE, $DOBJECT_ID, $DTYPE, $DINVOICE_TITLE, $DRECEIVER_NAME, $DRECEIVER_PHONE, $DRECEIVER_ADDRESS, $DREMARK, $DFREIGHT, $DCONTENT_TYPE){
        $cmd=  static::query()->createCommand('call P_WXMALL_INVOICE_DETAIL(
        :DOBJECT_TYPE,:DOBJECT_ID,:DTYPE,:DINVOICE_TITLE,:DRECEIVER_NAME
        ,:DRECEIVER_PHONE,:DRECEIVER_ADDRESS,:DREMARK,:DFREIGHT,:DCONTENT_TYPE,:RESULTS)');
        $cmd->bindValue(':DOBJECT_TYPE',$DOBJECT_TYPE);
        $cmd->bindValue(':DOBJECT_ID',$DOBJECT_ID);
        $cmd->bindValue(':DTYPE',$DTYPE);
        $cmd->bindValue(':DINVOICE_TITLE',$DINVOICE_TITLE);
        $cmd->bindValue(':DRECEIVER_NAME',$DRECEIVER_NAME);
        $cmd->bindValue(':DRECEIVER_PHONE',$DRECEIVER_PHONE);
        $cmd->bindValue(':DRECEIVER_ADDRESS',$DRECEIVER_ADDRESS);
        $cmd->bindValue(':DREMARK',$DREMARK);
        $cmd->bindValue(':DFREIGHT',$DFREIGHT);
        $cmd->bindValue(':DCONTENT_TYPE',$DCONTENT_TYPE);


        $cmd->bindParam(':RESULTS', $RESULTS,\PDO::PARAM_STR,50);
        $cmd->execute();
//        Yii::trace($orderid);
        return $RESULTS;
    }
    //查询字典表
    public static function get_dict_list($type){
        $cmd= static::query()->createCommand('select * from T_WXMALL_DICT where type=:type order by sort ');
        $cmd->bindValue(':type',$type);
        return $cmd->queryAll();
    }

    //校验bag订单号
    public static function verifyBagOrderCode($ORDERCODE,$user_id){
        $cmd= static::query()->createCommand('select count(1) from t_wxmall_dorabag_order where user_id=:user_id and code=:code');
        $cmd->bindValue(':user_id',$user_id);
        $cmd->bindValue(':code',$ORDERCODE);
        return $cmd->queryScalar();
    }

    //查询发票运费
    //object_type 开票对象 1：朵拉标准卡 2：朵拉礼品包
    //object_id  开票对象id  当object_type为1：   当object_type为2：此值为礼品包订单id(t_wxmall_dorabag_order表code
    public static function get_invoice_FREIGHT($object_type,$object_id){
        $cmd= static::query()->createCommand('select FREIGHT T_WXMALL_INVOICE where object_type=:object_type and object_id=:object_id ');
        $cmd->bindValue(':object_type',$object_type);
        $cmd->bindValue(':object_id',$object_id);
        $re=$cmd->queryScalar();
        if($re==false){
            $re=0;
        }
        return $re;
    }
    //查询发票信息
    public static function get_t_wxmall_invoice($object_type,$object_id){
        $cmd= static::query()->createCommand('select * from T_WXMALL_INVOICE where object_type=:object_type and object_id=:object_id');
        $cmd->bindValue(':object_type',$object_type);
        $cmd->bindValue(':object_id',$object_id);
        return $cmd->queryAll();
    }
    //查询发票信息--详情
    public static function get_t_wxmall_invoice_content($invoice_id){
        $cmd= static::query()->createCommand('select * from t_wxmall_invoice_content where invoice_id=:invoice_id');
        $cmd->bindValue(':invoice_id',$invoice_id);
        return $cmd->queryAll();
    }

    //获取朵拉卡信息
    public static function get_CardInfo($card_code_id){
        $cmd=  static::query()->createCommand('
            select t2.name, t2.valid_date_message, t1.create_datetime BOCREATE_DATETIME,t3.nick_name,0 GOODS_CODE
            from T_WXMALL_DORACARD_CODE t0
            left join T_WXMALL_ORDER t1 on t1.id=t0.order_id
            left join T_WXMALL_DORACARD t2 on t2.id=t0.card_id
            left join T_WXMALL_USER t3 on t3.id=t1.user_id
            where t0.id=:id');
        $cmd->bindValue(':id',$card_code_id);
        return $cmd->queryAll();
    }

    //获取朵拉币记录
    public static function get_DoracoinRecord($user_id){
        $cmd=  static::query()->createCommand('
            select t0.* from T_WXMALL_DORACOIN_RECORD t0
            where t0.USER_ID=:user_id
            order by t0.CREATE_DATETIME desc');
        $cmd->bindValue(':user_id',$user_id);
        return $cmd->queryAll();
    }

    //获取朵拉币记录
    public static function get_Current($user_id){
        $cmd=  static::query()->createCommand('
            select t0.* from T_WXMALL_DORACOIN_RECORD t0
            where t0.USER_ID=:user_id
            order by t0.CREATE_DATETIME desc');
        $cmd->bindValue(':user_id',$user_id);
        return $cmd->queryAll();
    }

    //获取首页页面数据
    //顶部广告
    public static function get_Home_banner(){
        $cmd=  static::query()->createCommand('select IMGURL,ADDURL from T_WXMALL_HOMEPAGE_CONFIG where SHOWTYPE=1');

        return $cmd->queryAll();
    }
    //5cell 当期推荐
    public static function get_Home_5cell(){
        $cmd=  static::query()->createCommand('select IMGURL,GOODSTYPE,GOODSID from T_WXMALL_HOMEPAGE_CONFIG where SHOWTYPE=3 order by STYLE');

        return $cmd->queryAll();
    }
    //3 goods 热销爆款
    public static function get_Home_3goods(){
        $cmd=  static::query()->createCommand('select g.GOODS_IMG_URL as IMGURL,c.GOODSCODE from T_WXMALL_HOMEPAGE_CONFIG c,t_wxmall_goods g where c.SHOWTYPE=4 and g.goods_code=c.goodscode');

        return $cmd->queryAll();
    }
    //new goods list 新品推荐
    public static function get_Home_newgoods(){
        //GOODS_IMG_URL
        $cmd=  static::query()->createCommand('select g.GOODS_IMG_URL as IMGURL,c.GOODSCODE,g.price,g.goods_name from T_WXMALL_HOMEPAGE_CONFIG c,t_wxmall_goods g
               where c.SHOWTYPE=5 and g.goods_code=c.goodscode');

        return $cmd->queryAll();
    }

    /**
     * 获取“卡片列表”界面banner
     * @return array
     */
    public static function getCardListBanner(){
        $cmd=  static::query()->createCommand('select IMGURL,ADDURL from T_WXMALL_HOMEPAGE_CONFIG where SHOWTYPE=6');

        return $cmd->queryAll();
    }

    /**
     * 获取分销比例
     * @return array
     */
    public static function getFxDiscount(){
        $cmd=  static::query()->createCommand('select * from t_wxmall_discount order by buylower');

        return $cmd->queryAll();
    }
//地址管理---------------------------------------------------------------------------------------------begin
    //地址管理
    public  static function getUserAddressList($user_id){
        $cmd=  static::query()->createCommand('select * from t_wx_address where user_id=:user_id');
        $cmd->bindValue(':user_id',$user_id);
        return $cmd->queryAll();
    }
    //获取地址
    public static function getAddress($id,$user_id){

        $cmd = static::query()->createCommand('select * from  t_wx_address  where id=:id and user_id=:user_id');
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':user_id', $user_id);
        return  $cmd->queryAll();
    }
    //获取需要使用的地址
    public static function getUserAddress($address_id,$user_id){
        if($address_id==''){
            $cmd = static::query()->createCommand('select * from  t_wx_address  where is_default=1 and user_id=:user_id');
            $cmd->bindValue(':user_id', $user_id);
            $re= $cmd->queryAll();
            if(count($re)==0){//如果没有默认地址,取第一个并设置为默认地址
                $cmd = static::query()->createCommand('select * from  t_wx_address where rownum=1 and user_id=:user_id');
                $cmd->bindValue(':user_id', $user_id);
                $re= $cmd->queryAll();
                if(count($re)>0){
                    self::setAddressDefault($re[0]['ID'],$user_id);
                    return $re[0];
                }else{
                    return false;
                }
            }else{
                return $re[0];
            }
        }else {
            $cmd = static::query()->createCommand('select * from  t_wx_address  where id=:id and user_id=:user_id');
            $cmd->bindValue(':id', $address_id);
            $cmd->bindValue(':user_id', $user_id);
            $re = $cmd->queryAll();
            return count($re)>0 ? $re[0] : false;
        }
    }
    /**
     * 获取地区：省
     * @return array
     */
    public static function getAreaList_sheng()
    {
        $sql = "SELECT code,name,id FROM t_wx_area WHERE parent_id = '' or parent_id is null  ORDER BY code asc";
        $cmd = \Yii::$app->db->createCommand($sql);
        $result = $cmd->queryAll();
        return $result;

    }


    /**
     * 获取地区：市 区
     * @return array
     */
    public static function getAreaList_other($parent_id)
    {
        $sql = "SELECT code,name,id FROM t_wx_area WHERE parent_id = :parent_id ORDER BY code asc";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':parent_id', $parent_id);
        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 新增/更新用户收货地址信息
     * @param $userid
     * @param $rece_name
     * @param $rece_phone
     * @param $rece_address
     * @param $sheng
     * @param $shi
     * @param $qu
     * @return int
     * @throws \yii\db\Exception
     */
    public static function saveReceiverAddress($id,$userid, $rece_name, $rece_phone, $rece_address, $sheng, $shi, $qu)
    {

        if ($id == '') {//add
            $id=static::db_uuid();
            $cmd = static::query()->createCommand('insert into t_wx_address (id,user_id,name,phone,address,province,city,region,is_default)
                 VALUES (:id,:user_id,:name,:phone,:address,:province,:city,:region,0)');
            $cmd->bindValue(':id', $id);
            $cmd->bindValue(':user_id', $userid);
        } else {//update
            $cmd = static::query()->createCommand('update t_wx_address set name=:name,phone=:phone,address=:address
            ,province=:province,city=:city,region=:region
                   where id=:id');
            $cmd->bindValue(':id', $id);
        }


        $cmd->bindValue(':name', $rece_name);
        $cmd->bindValue(':phone', $rece_phone);
        $cmd->bindValue(':address', $rece_address);
        $cmd->bindValue(':province', $sheng);
        $cmd->bindValue(':city', $shi);
        $cmd->bindValue(':region', $qu);
        $result=$cmd->execute() >0;
        if($result){
//            $cmd = static::query()->createCommand('select count(1) from t_wx_address where is_default=1 and user_id=:user_id');
//            $cmd->bindValue(':user_id', $userid);
//            $re=$cmd->queryScalar();
//            if($re==0){
                self::setAddressDefault($id,$userid);
//            }
        }
        return $result;

    }
    //删除地址
    public static function delAddress($id,$user_id){

        $cmd = static::query()->createCommand('delete from  t_wx_address  where id=:id and  user_id=:user_id');
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':user_id', $user_id);
        return  $cmd->execute() >0;
    }
    //设置默认地址
    public static function setAddressDefault($id,$user_id){
        $cmd = static::query()->createCommand('update t_wx_address set is_default=0  where user_id=:user_id');
        $cmd->bindValue(':user_id', $user_id);
        $cmd->execute();
        $cmd = static::query()->createCommand('update t_wx_address set is_default=1  where id=:id');
        $cmd->bindValue(':id', $id);
        return  $cmd->execute() >0;
    }
//地址管理-----------------------------------------------------------end
}